﻿using Dapper;
using Sprout.Core.Extensions;
using Sprout.Core.Paging;
using Sprout.Core.Utils;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Threading.Tasks;

namespace Sprout.Repository
{
    /// <summary>
    /// Dapper 处理类
    /// </summary>
    internal sealed class DapperProcess
    {
        #region 实例
        private static volatile DapperProcess _dapperProcess;
        private static readonly object _locker = new object();

        /// <summary>
        /// 实例
        /// </summary>
        public static DapperProcess Instance
        {
            get
            {
                if (_dapperProcess == null)
                {
                    lock (_locker)
                    {
                        if (_dapperProcess == null)
                        {
                            _dapperProcess = new DapperProcess();
                        }
                    }
                }
                return _dapperProcess;
            }
        }
        #endregion

        #region 获取数据库连接
        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <param name="dataSource"></param>
        /// <returns></returns>
        public IDbConnection GetConnection(string dataSource = null)
        {
            return DataSetting.Instance.GetConnection(dataSource);
        }

        #endregion

        #region 执行

        #region SQL执行
        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteSql(IDbConnection connection, string sql, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var result = connection.Execute(sql, parameters, transaction, commandTimeout, commandType);
            return result;
        }
        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>受影响的行数</returns>
        public async Task<int> ExecuteSqlAsync(IDbConnection connection, string sql, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var result = await connection.ExecuteAsync(sql, parameters, transaction, commandTimeout, commandType);
            return result;
        }
        /// <summary>
        /// 执行返回自增ID
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增ID</returns>
        public long ExecuteReturnIdSql(IDbConnection connection, string sql, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            connection.Execute(sql, parameters, transaction, commandTimeout, commandType);
            var id = GetIdentity(connection, transaction);
            return id;
        }
        /// <summary>
        /// 执行返回自增ID
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增ID</returns>
        public async Task<long> ExecuteReturnIdSqlAsync(IDbConnection connection, string sql, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            await connection.ExecuteAsync(sql, parameters, transaction, commandTimeout, commandType);
            var id = await GetIdentityAsync(connection, transaction);
            return id;
        }
        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>受影响的行数</returns>
        public int ExecuteSql(string sql, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource, true);
            connection = tuple.Item1;
            transaction = tuple.Item2;

            return ExecuteSql(connection, sql, parameters, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>受影响的行数</returns>
        public async Task<int> ExecuteSqlAsync(string sql, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource, true);
            connection = tuple.Item1;
            transaction = tuple.Item2;

            return await ExecuteSqlAsync(connection, sql, parameters, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行返回自增ID
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增ID</returns>
        public long ExecuteReturnIdSql(string sql, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            dataSource = GetDataSource(dataSource, true);
            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource, true);
            connection = tuple.Item1;
            transaction = tuple.Item2;

            long id = 0;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                id = ExecuteReturnIdSql(connection, sql, parameters, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return id;
        }

        /// <summary>
        /// 执行返回自增ID
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增ID</returns>
        public async Task<long> ExecuteReturnIdSqlAsync(string sql, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            dataSource = GetDataSource(dataSource, true);
            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource, true);
            connection = tuple.Item1;
            transaction = tuple.Item2;

            long id = 0;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                id = await ExecuteReturnIdSqlAsync(connection, sql, parameters, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return id;
        }

        #endregion

        #region 模板执行
        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>受影响的行数</returns>
        public int Execute(IDbConnection connection, string statementId, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return ExecuteSql(connection, sql, parameters, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>受影响的行数</returns>
        public async Task<int> ExecuteAsync(IDbConnection connection, string statementId, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await ExecuteSqlAsync(connection, sql, parameters, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 执行返回自增ID
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增ID</returns>
        public long ExecuteReturnId(IDbConnection connection, string statementId, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return ExecuteReturnIdSql(connection, sql, parameters, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行返回自增ID
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增ID</returns>
        public async Task<long> ExecuteReturnIdAsync(IDbConnection connection, string statementId, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await ExecuteReturnIdSqlAsync(connection, sql, parameters, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>受影响的行数</returns>
        public int Execute(string statementId, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return ExecuteSql(sql, parameters, dataSource, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行
        /// </summary>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>受影响的行数</returns>
        public async Task<int> ExecuteAsync(string statementId, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await ExecuteSqlAsync(sql, parameters, dataSource, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行返回自增ID
        /// </summary>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增ID</returns>
        public long ExecuteReturnId(string statementId, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return ExecuteReturnIdSql(sql, parameters, dataSource, commandTimeout, commandType);
        }

        /// <summary>
        /// 执行返回自增ID
        /// </summary>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增ID</returns>
        public async Task<long> ExecuteReturnIdAsync(string statementId, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await ExecuteReturnIdSqlAsync(sql, parameters, dataSource, commandTimeout, commandType);
        }

        #endregion

        #endregion

        #region 查询

        #region SQL查询
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public IList<T> QuerySql<T>(IDbConnection connection, string sql, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            IList<T> list;
            using (IDataReader reader = connection.ExecuteReader(sql, parameters, transaction, commandTimeout, commandType))
            {
                list = Query<T>(reader);
            }
            return list;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public async Task<IList<T>> QuerySqlAsync<T>(IDbConnection connection, string sql, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            IList<T> list;
            IDataReader reader = await connection.ExecuteReaderAsync(sql, parameters, transaction, commandTimeout, commandType);
            using (reader)
            {
                list = Query<T>(reader);
            }
            return list;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>实体</returns>
        public T QueryFirstSql<T>(IDbConnection connection, string sql, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            T value;
            using (IDataReader reader = connection.ExecuteReader(sql, parameters, transaction, commandTimeout, commandType))
            {
                value = Query<T>(reader, true).FirstOrDefault();
            }
            return value;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>实体</returns>
        public async Task<T> QueryFirstSqlAsync<T>(IDbConnection connection, string sql, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            T value;
            IDataReader reader = await connection.ExecuteReaderAsync(sql, parameters, transaction, commandTimeout, commandType);
            using (reader)
            {
                value = Query<T>(reader, true).FirstOrDefault();
            }
            return value;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public IList<T> QuerySql<T>(string sql, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            IList<T> list;
            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource);
            connection = tuple.Item1;
            transaction = tuple.Item2;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                list = QuerySql<T>(connection, sql, parameters, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return list;
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public async Task<IList<T>> QuerySqlAsync<T>(string sql, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            IList<T> list;
            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource);
            connection = tuple.Item1;
            transaction = tuple.Item2;

            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                list = await QuerySqlAsync<T>(connection, sql, parameters, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return list;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>实体</returns>
        public T QueryFirstSql<T>(string sql, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource);
            connection = tuple.Item1;
            transaction = tuple.Item2;
            T value;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                value = QueryFirstSql<T>(connection, sql, parameters, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return value;
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>实体</returns>
        public async Task<T> QueryFirstSqlAsync<T>(string sql, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource);
            connection = tuple.Item1;
            transaction = tuple.Item2;
            T value;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                value = await QueryFirstSqlAsync<T>(connection, sql, parameters, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }

            return value;
        }

        #endregion

        #region 模板查询
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public IList<T> Query<T>(IDbConnection connection, string statementId, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return QuerySql<T>(connection, sql, parameters, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public async Task<IList<T>> QueryAsync<T>(IDbConnection connection, string statementId, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await QuerySqlAsync<T>(connection, sql, parameters, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public T QueryFirst<T>(IDbConnection connection, string statementId, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return QueryFirstSql<T>(connection, sql, parameters, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public async Task<T> QueryFirstAsync<T>(IDbConnection connection, string statementId, object parameters = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await QueryFirstSqlAsync<T>(connection, sql, parameters, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public IList<T> Query<T>(string statementId, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return QuerySql<T>(sql, parameters, dataSource, commandTimeout, commandType);
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>数据集合</returns>
        public async Task<IList<T>> QueryAsync<T>(string statementId, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await QuerySqlAsync<T>(sql, parameters, dataSource, commandTimeout, commandType);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="statementId">SQL语句ID</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>实体</returns>
        public T QueryFirst<T>(string statementId, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return QueryFirstSql<T>(sql, parameters, dataSource, commandTimeout, commandType);
        }

        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="statementId">SQL语句ID</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>实体</returns>
        public async Task<T> QueryFirstAsync<T>(string statementId, object parameters = null, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await QueryFirstSqlAsync<T>(sql, parameters, dataSource, commandTimeout, commandType);
        }

        #endregion

        #endregion

        #region 分页查询

        #region SQL分页
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="isCount">是否获取总数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public PagedList<T> QueryPagingSql<T>(IDbConnection connection, string sql, PageParameter parameters, bool isCount, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var pagingSql = GetPagingSql(connection, sql, parameters);
            var countSql = GetCountSql(connection, sql, isCount);

            return QueryPagingSql<T>(connection, pagingSql, countSql, parameters, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="isCount">是否获取总数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public async Task<PagedList<T>> QueryPagingSqlAsync<T>(IDbConnection connection, string sql, PageParameter parameters, bool isCount, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            var pagingSql = GetPagingSql(connection, sql, parameters);
            var countSql = GetCountSql(connection, sql, isCount);

            return await QueryPagingSqlAsync<T>(connection, pagingSql, countSql, parameters, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="countSql">查询总数SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public PagedList<T> QueryPagingSql<T>(IDbConnection connection, string sql, string countSql, PageParameter parameters, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            PagedList<T> result = new PagedList<T>() { PageSize = parameters.PageSize };

            using (IDataReader reader = connection.ExecuteReader(sql, parameters, transaction))
            {
                var list = Query<T>(reader);
                result.PageData = list;
            }
            if (string.IsNullOrEmpty(countSql) == false)
            {
                using (IDataReader reader = connection.ExecuteReader(countSql, parameters, transaction))
                {
                    result.TotalCount = Query<int>(reader, true).First();
                }
            }

            return result;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">SQL语句</param>
        /// <param name="countSql">查询总数SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public async Task<PagedList<T>> QueryPagingSqlAsync<T>(IDbConnection connection, string sql, string countSql, PageParameter parameters, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            PagedList<T> result = new PagedList<T>() { PageSize = parameters.PageSize };
            IDataReader reader = await connection.ExecuteReaderAsync(sql, parameters, transaction);
            using (reader)
            {
                var list = Query<T>(reader);
                result.PageData = list;
            }
            if (string.IsNullOrEmpty(countSql) == false)
            {
                reader = await connection.ExecuteReaderAsync(countSql, parameters, transaction);
                result.TotalCount = Query<int>(reader, true).First();
            }
            return result;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="isCount">是否获取总数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public PagedList<T> QueryPagingSql<T>(string sql, PageParameter parameters, bool isCount, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            PagedList<T> result;
            dataSource = GetDataSource(dataSource);
            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource);
            connection = tuple.Item1;
            transaction = tuple.Item2;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                result = QueryPagingSql<T>(connection, sql, parameters, isCount, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return result;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="isCount">是否获取总数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public async Task<PagedList<T>> QueryPagingSqlAsync<T>(string sql, PageParameter parameters, bool isCount, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            PagedList<T> result;
            dataSource = GetDataSource(dataSource);

            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource);
            connection = tuple.Item1;
            transaction = tuple.Item2;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                result = await QueryPagingSqlAsync<T>(connection, sql, parameters, isCount, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return result;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="countSql">查询总数SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public PagedList<T> QueryPagingSql<T>(string sql, string countSql, PageParameter parameters, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            PagedList<T> result;
            dataSource = GetDataSource(dataSource);
            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource);
            connection = tuple.Item1;
            transaction = tuple.Item2;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                result = QueryPagingSql<T>(connection, sql, countSql, parameters, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return result;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql">SQL语句</param>
        /// <param name="countSql">查询总数SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public async Task<PagedList<T>> QueryPagingSqlAsync<T>(string sql, string countSql, PageParameter parameters, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            PagedList<T> result;
            dataSource = GetDataSource(dataSource);
            bool wasClosed = false;
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            var tuple = GetConnAndTrans(dataSource);
            connection = tuple.Item1;
            transaction = tuple.Item2;
            try
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                    wasClosed = true;
                }
                result = await QueryPagingSqlAsync<T>(connection, sql, countSql, parameters, transaction, commandTimeout, commandType);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (wasClosed)
                {
                    connection.Close();
                    connection.Dispose();
                }
            }
            return result;
        }

        #endregion

        #region 模板分页
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="isCount">是否获取总数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public PagedList<T> QueryPaging<T>(IDbConnection connection, string statementId, PageParameter parameters, bool isCount, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return QueryPagingSql<T>(connection, sql, parameters, isCount, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="isCount">是否获取总数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public async Task<PagedList<T>> QueryPagingAsync<T>(IDbConnection connection, string statementId, PageParameter parameters, bool isCount, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await QueryPagingSqlAsync<T>(connection, sql, parameters, isCount, transaction, commandTimeout, commandType);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="countStatementId">查询总数statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public PagedList<T> QueryPaging<T>(IDbConnection connection, string statementId, string countStatementId, PageParameter parameters, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            string countSql = StatementService.Instance.GetStatementSql(countStatementId, parameters);
            return QueryPagingSql<T>(connection, sql, countSql, parameters, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="connection">数据库连接</param>
        /// <param name="statementId">statementId</param>
        /// <param name="countStatementId">查询总数statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public async Task<PagedList<T>> QueryPagingAsync<T>(IDbConnection connection, string statementId, string countStatementId, PageParameter parameters, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            string countSql = StatementService.Instance.GetStatementSql(countStatementId, parameters);
            return await QueryPagingSqlAsync<T>(connection, sql, countSql, parameters, transaction, commandTimeout, commandType);
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="isCount">是否获取总数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public PagedList<T> QueryPaging<T>(string statementId, PageParameter parameters, bool isCount, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return QueryPagingSql<T>(sql, parameters, isCount, dataSource, commandTimeout, commandType);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="isCount">是否获取总数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public async Task<PagedList<T>> QueryPagingAsync<T>(string statementId, PageParameter parameters, bool isCount, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return await QueryPagingSqlAsync<T>(sql, parameters, isCount, dataSource, commandTimeout, commandType);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="statementId">statementId</param>
        /// <param name="countStatementId">查询总数statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public PagedList<T> QueryPaging<T>(string statementId, string countStatementId, PageParameter parameters, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            string countSql = StatementService.Instance.GetStatementSql(countStatementId, parameters);

            return QueryPagingSql<T>(sql, countSql, parameters, dataSource, commandTimeout, commandType);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="statementId">statementId</param>
        /// <param name="countStatementId">查询总数statementId</param>
        /// <param name="parameters">参数</param>
        /// <param name="dataSource">数据源</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>分页数据结果</returns>
        public async Task<PagedList<T>> QueryPagingAsync<T>(string statementId, string countStatementId, PageParameter parameters, string dataSource = null, int? commandTimeout = null, CommandType? commandType = null)
        {

            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            string countSql = StatementService.Instance.GetStatementSql(countStatementId, parameters);

            return await QueryPagingSqlAsync<T>(sql, countSql, parameters, dataSource, commandTimeout, commandType);
        }

        #endregion

        #endregion

        #region 获取执行SQL
        /// <summary>
        /// 获取运行时SQL
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>SQL语句</returns>
        public string GetRuningSqlBySql(string sql, object parameters = null)
        {
            if (parameters == null)
            {
                return sql;
            }
            sql = sql.TrimEnd(';');
            //sql = ReplaceSqlLast(sql, ";");
            sql = sql + " ";

            if ((parameters is string) == false && parameters is System.Collections.IEnumerable)
            {
                StringBuilder sb = new StringBuilder();
                var list = parameters as System.Collections.IEnumerable;
                foreach (var item in list)
                {
                    sb.Append(GetRuningSqlForClass(sql, item) + ";");
                }

                return sb.ToString();
            }
            else
            {
                sql = GetRuningSqlForClass(sql, parameters);
                return sql;
            }
        }
        /// <summary>
        /// 获取运行时SQL
        /// </summary>
        /// <param name="statementId">statementId</param>
        /// <param name="parameters">参数</param>
        /// <returns>SQL语句</returns>
        public string GetRuningSql(string statementId, object parameters = null)
        {
            string sql = StatementService.Instance.GetStatementSql(statementId, parameters);
            return GetRuningSqlBySql(sql, parameters);
        }

        #endregion

        #region 内部方法

        #region 获取DataSource
        /// <summary>
        /// 获取DataSource
        /// </summary>
        /// <param name="dataSource">数据源</param>
        /// <param name="isWrite">有写操作</param>
        /// <returns>数据源</returns>
        private string GetDataSource(string dataSource, bool isWrite = false)
        {
            var setting = DataSetting.Instance.GetConnectionOption(dataSource);
            if (setting.IsReadOnly)
            {
                if (isWrite)
                {
                    throw new Exception("读库不能进行写操作");
                }
                //如果开启了全局事务，则指定写库
                var isTransaction = TransactionService.Instance.IsTransaction();
                if (isTransaction)
                {
                    if (string.IsNullOrEmpty(setting.WriteName))
                    {
                        throw new Exception("事务中不允许使用只读库，请指定写库的数据源");
                    }
                    return setting.WriteName;
                }
            }
            return dataSource;
        }
        #endregion

        #region 获取数据库连接和事务
        /// <summary>
        /// 获取数据库连接和事务
        /// </summary>
        /// <param name="dataSource">数据源</param>
        /// <param name="isWrite">有写操作</param>
        /// <returns></returns>
        private Tuple<IDbConnection, IDbTransaction> GetConnAndTrans(string dataSource, bool isWrite = false)
        {
            dataSource = GetDataSource(dataSource, isWrite);
            IDbConnection connection = null;
            IDbTransaction transaction = null;
            if (TransactionService.Instance.IsTransaction())
            {
                var transactionMap = TransactionService.Instance.GetOrAddTransaction(dataSource);
                connection = transactionMap.Connection;
                transaction = transactionMap.Transaction;
            }
            else
            {
                connection = DataSetting.Instance.GetConnection(dataSource);
            }

            return new Tuple<IDbConnection, IDbTransaction>(connection, transaction);
        }
        #endregion

        #region 查询封装
        /// <summary>
        /// 获取自增SQL语句
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <returns>SQL语句</returns>
        private string GetIdentitySql(IDbConnection connection)
        {
            string sql = "";
            if (MySqlHelper.IsConnection(connection))
            {
                sql = "select @@identity;";
            }
            else if (SqlServerHelper.IsConnection(connection))
            {
                sql = "select SCOPE_IDENTITY()";
            }
            else
            {
                throw new Exception("不支持的数据库");
            }
            return sql;
        }

        /// <summary>
        /// 获取自增ID
        /// </summary>
        /// <param name="connection">连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增</returns>
        private long GetIdentity(IDbConnection connection, IDbTransaction transaction, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = GetIdentitySql(connection);

            long id = 0;
            using (IDataReader reader = connection.ExecuteReader(sql, null, transaction, commandTimeout, commandType))
            {
                id = Query<long>(reader, true).FirstOrDefault();
            }
            return id;
        }
        /// <summary>
        /// 获取自增ID
        /// </summary>
        /// <param name="connection">连接</param>
        /// <param name="transaction">事务</param>
        /// <param name="commandTimeout">超时时间（秒）</param>
        /// <param name="commandType">执行类型</param>
        /// <returns>自增</returns>
        private async Task<long> GetIdentityAsync(IDbConnection connection, IDbTransaction transaction, int? commandTimeout = null, CommandType? commandType = null)
        {
            string sql = GetIdentitySql(connection);
            IDataReader reader = await connection.ExecuteReaderAsync(sql, null, transaction, commandTimeout, commandType);
            long id = 0;
            using (reader)
            {
                id = Query<long>(reader, true).FirstOrDefault();
            }
            return id;
        }

        /// <summary>
        /// 设置值
        /// </summary>
        /// <param name="entity">铺晚上</param>
        /// <param name="reader">DataReader</param>
        /// <param name="property">属性</param>
        /// <param name="data">值</param>
        private void SetValue(object entity, IDataReader reader, PropertyInfo property, object data)
        {
            if (data != DBNull.Value)
            {
                try
                {
                    if (property.PropertyType.IsGenericType && property.PropertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                    {
                        property.SetValue(entity, ObjectChangeTypeHelper.ChangeType(data, property.PropertyType.GetGenericArguments()[0]), null);
                    }
                    else
                    {
                        property.SetValue(entity, ObjectChangeTypeHelper.ChangeType(data, property.PropertyType), null);
                    }
                }
                catch
                {
                    throw new Exception($"设置 {property.Name} 值失败，请检查类型");
                }
            }
        }
        /// <summary>
        /// 查询
        /// </summary>
        /// <typeparam name="T">泛型</typeparam>
        /// <param name="reader">DataReader</param>
        /// <param name="isFirst">是否只查询单条数据</param>
        /// <returns>数据集合</returns>
        private IList<T> Query<T>(IDataReader reader, bool isFirst = false)
        {
            IList<T> list = new List<T>();
            var fieldCount = reader.FieldCount;

            var type = typeof(T);

            //如果是值类型或string
            if (type.IsValueType || type == typeof(string))
            {
                while (reader.Read())
                {
                    var data = reader.GetValue(0);
                    if (data != DBNull.Value)
                    {
                        T value = (T)ObjectChangeTypeHelper.ChangeType(reader[0], typeof(T));
                        list.Add(value);
                    }
                    else
                    {
                        list.Add(default(T));
                    }

                    if (isFirst)
                    {
                        break;
                    }
                }
            }
            //如果是字典类型 或 动态对象类型
            else if (type == typeof(Dictionary<string, object>)
                || type == typeof(JObject)
                || type == typeof(object)
                || type == typeof(System.Dynamic.ExpandoObject))
            {
                while (reader.Read())
                {
                    var kv = new Dictionary<string, object>();
                    for (var i = 0; i < fieldCount; i++)
                    {
                        var columnName = reader.GetName(i);

                        kv.Add(columnName, reader.GetValue(i));
                    }
                    list.Add(kv.ToJsonString().FromJsonString<T>());

                    if (isFirst)
                    {
                        break;
                    }
                }
            }
            else
            {
                var properties = typeof(T).GetProperties();
                Dictionary<string, PropertyInfo> propertyDic = new Dictionary<string, PropertyInfo>();
                while (reader.Read())
                {
                    var entity = Activator.CreateInstance(type);
                    for (var i = 0; i < fieldCount; i++)
                    {
                        var columnName = reader.GetName(i);
                        if (propertyDic.ContainsKey(columnName))
                        {
                            var property = propertyDic[columnName];
                            if (property != null)
                            {
                                var data = reader.GetValue(i);
                                SetValue(entity, reader, property, data);
                            }
                        }
                        else
                        {
                            var PropertyName = columnName.Replace("_", "");

                            foreach (var property in properties)
                            {
                                if (property.Name.ToLower() == columnName.ToLower() || property.Name.ToLower() == PropertyName.ToLower())
                                {
                                    if (property.CanWrite)//判断属性是否可写
                                    {
                                        var data = reader.GetValue(i);
                                        SetValue(entity, reader, property, data);
                                    }
                                    if (propertyDic.ContainsKey(columnName) == false)
                                    {
                                        propertyDic.Add(columnName, property);
                                    }

                                    break;
                                }
                            }
                            if (propertyDic.ContainsKey(columnName) == false)
                            {
                                propertyDic.Add(columnName, null);
                            }
                        }
                    }
                    list.Add((T)entity);

                    if (isFirst)
                    {
                        break;
                    }
                }
            }
            return list;
        }


        #endregion

        #region 获取分页语句
        /// <summary>
        /// 获取分页语句
        /// </summary>
        /// <param name="connection">数据库</param>
        /// <param name="sql">原始SQL</param>
        /// <param name="parameters">参数</param>
        /// <returns></returns>
        private string GetPagingSql(IDbConnection connection, string sql, PageParameter parameters)
        {
            string pagingSql = sql.Trim().TrimEnd(';');
            if (MySqlHelper.IsConnection(connection))
            {
                pagingSql += $" limit {parameters.SkipIndex}, {parameters.PageSize} ";
            }
            else if (SqlServerHelper.IsConnection(connection))
            {
                int orderByIndex = 0;
                //去掉select 
                pagingSql = pagingSql.Substring(6);

                orderByIndex = pagingSql.ToLower().LastIndexOf("order by");
                string orderBySql = string.Empty;
                if (orderByIndex == -1)
                {
                    orderBySql = "order by RAND()";
                }
                else
                {
                    orderBySql = pagingSql.Substring(orderByIndex);
                    pagingSql = pagingSql.Substring(0, orderByIndex);
                }
                pagingSql = $"select * from (select row_number() over({orderBySql}) as rowNum,{pagingSql} ) t where t.rowNum between {parameters.StartIndex} and {parameters.EndIndex} order by t.rowNum ";
            }
            else if (OracleHelper.IsConnection(connection))
            {
                pagingSql = $"SELECT * FROM ({pagingSql}) a WHERE ROWNUM between {parameters.StartIndex} and {parameters.EndIndex} order by ROWNUM ";
            }
            else
            {
                throw new Exception("不支持的数据库");
            }

            return pagingSql;
        }
        /// <summary>
        /// 查询求总数SQL
        /// </summary>
        /// <param name="connection">数据库</param>
        /// <param name="sql">原始SQL</param>
        /// <param name="isCount">是否获取总数</param>
        /// <returns></returns>
        private string GetCountSql(IDbConnection connection, string sql, bool isCount)
        {
            if (isCount == false)
            {
                return string.Empty;
            }
            sql = sql.Trim().TrimEnd(';');
            if (MySqlHelper.IsConnection(connection))
            {
                return $" select count(*) from ({sql}) a ";
            }
            else if (SqlServerHelper.IsConnection(connection))
            {
                return $" select count(*) from ({sql}) a ";
            }
            else if (OracleHelper.IsConnection(connection))
            {
                return $" select count(*) from ({sql}) a ";
            }
            else
            {
                throw new Exception("不支持的数据库");
            }
        }

        #endregion

        #region 替换参数
        /// <summary>
        /// 处理参数值
        /// </summary>
        /// <param name="value"></param>
        /// <returns></returns>
        private object GetParamsValue(object value)
        {
            if (value == null)
                value = "null";
            else if (value is Enum)
                value = (int)value;
            else if (value is Boolean)
                value = Convert.ToInt32(value);
            else if (value is int
                || value is short
                || value is long
                || value is uint
                || value is ushort
                || value is ulong
                || value is double
                || value is float
                || value is decimal
                )
            {

            }
            else
                value = $"'{value}'";
            return value;
        }
        /// <summary>
        /// 替换参数
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        private string GetRuningSqlForClass(string sql, object parameters)
        {
            Type type = parameters.GetType();

            foreach (var property in type.GetProperties())
            {
                if (property.CanRead)
                {
                    var value = property.GetValue(parameters, null);
                    if (value is System.Collections.IEnumerable && (value is string) == false)
                    {
                        var list = value as System.Collections.IEnumerable;
                        StringBuilder sb = new StringBuilder();
                        foreach (var item in list)
                        {
                            sb.Append($"{GetParamsValue(item)},");
                        }
                        string arrStr = sb.ToString().TrimEnd(',');
                        sql = sql.Replace($"@{ property.Name} ", $"({arrStr}) ");
                        sql = sql.Replace($"@{ property.Name},", $"({arrStr}),");
                    }
                    else
                    {
                        value = GetParamsValue(value);
                        sql = sql.Replace($"@{ property.Name} ", $"{value} ");
                        sql = sql.Replace($"@{ property.Name},", $"{value},");
                        sql = sql.Replace($"@{ property.Name})", $"{value})");
                    }
                }
            }

            return sql;
        }
        #endregion

        #endregion
    }
}
